package com.css.zfzx.fxpg.modules.levelmap.casualty.repository;

import com.css.bpm.platform.utils.PlatformDateUtils;
import com.css.bpm.platform.utils.PlatformSessionUtils;
import com.css.bpm.platform.utils.UUIDGenerator;
import com.css.zfzx.fxpg.modules.levelmap.casualty.repository.entity.FxCasualtyLevelMapEntity;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.List;

/**
 * 初始化数据
 * 解决上万条数据插入慢的问题
 *
 * @Author leon
 * @Date: 2021/2/2 10:24 上午
 */
@Repository
@PropertySource("application-dev.yml")
public class FxCasualtyLevelMapImport {
    /**
     * 数据源
     * 10.19.41.111
     */
    @Value("${spring.datasource.url}")
    private String url = "";
    @Value("${spring.datasource.username}")
    private String name = "";
    @Value("${spring.datasource.password}")
    private String pwd = "";
    private static Connection conn;
    private static PreparedStatement ps;

    /**
     * 获取数据库连接
     * SQL语句
     */
    private Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }


    /**
     * sql 批量插入
     *
     * @return
     */
    public int sqlBatchSaveEntities(List<FxCasualtyLevelMapEntity> list) throws Exception {
        int count = 0;

        conn = getConnection();

        List<FxCasualtyLevelMapEntity> subList = null;
        int total = list.size(), num = 5000, time = total / num + 1, from = 0, to = 0;
        for (int i = 0; i < time; i++) {
            from = i * num;
            to = (i == time - 1) ? total : (i + 1) * num;
            subList = list.subList(from, to);

            StringBuffer sql = new StringBuffer("insert into fx_casualty_level_map (uuid, province, city, area, affiliated_file_name, coordinate, level, " +
                    "extend1, extend2, extend3, extend4, extend5, extend6, extend7, extend8, extend9, extend10, " +
                    "object_code, partition_flag, create_user, create_time ) "
                    + "values ");

            for (FxCasualtyLevelMapEntity entity : subList) {
                count++;
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String affiliatedFileName = entity.getAffiliatedFileName();
                String coordinate = entity.getCoordinate();
                Integer level = entity.getLevel();
                String extend1 = entity.getExtend1(); extend1 = (extend1 != null ? extend1 : "");
                String extend2 = entity.getExtend2(); extend2 = (extend2 != null ? extend2 : "");
                String extend3 = entity.getExtend3(); extend3 = (extend3 != null ? extend3 : "");
                String extend4 = entity.getExtend4(); extend4 = (extend4 != null ? extend4 : "");
                String extend5 = entity.getExtend5(); extend5 = (extend5 != null ? extend5 : "");
                String extend6 = entity.getExtend6(); extend6 = (extend6 != null ? extend6 : "");
                String extend7 = entity.getExtend7(); extend7 = (extend7 != null ? extend7 : "");
                String extend8 = entity.getExtend8(); extend8 = (extend8 != null ? extend8 : "");
                String extend9 = entity.getExtend9(); extend9 = (extend9 != null ? extend9 : "");
                String extend10 = entity.getExtend10(); extend10 = (extend10 != null ? extend10 : "");

                String objectCode = entity.getObjectCode();
                objectCode = (objectCode != null ? objectCode : "");
                String partitionFlag = entity.getPartitionFlag();
                partitionFlag = (partitionFlag != null ? partitionFlag : "");
                String createUser = PlatformSessionUtils.getUserId();
                createUser = (createUser != null ? createUser : "");
                Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();
                /*String updateUser = PlatformSessionUtils.getUserId();
                updateUser = (updateUser != null ? updateUser : "");
                Timestamp updateTime = PlatformDateUtils.getCurrentTimestamp();*/

                sql.append("('" + id + "','" + province + "','" + city + "','" + area + "','" + affiliatedFileName + "','" + coordinate + "','" + level +
                        "','" + extend1 + "','" + extend2 + "','" + extend3 + "','" + extend4 + "','" + extend5 + "','" + extend6 + "','" + extend7 + "','" + extend8 + "','" + extend9 + "','" + extend10
                        + "','" + objectCode + "','" + partitionFlag + "','" + createUser + "','" + createTime + "'),");
            }
            String sql_str = sql.toString();
            sql_str = sql_str.substring(0, sql_str.length() - 1);
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
            if (ps != null) ps.close();
        }
        if (conn != null) conn.close();
        return count;
    }

    public int sqlBatchDeleteEntities(List<FxCasualtyLevelMapEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            StringBuffer sql = new StringBuffer("DELETE FROM fx_casualty_level_map WHERE uuid IN ( ");
            // 上万条数据
            for (FxCasualtyLevelMapEntity entity : list) {
                count++;
                String id = entity.getUuid();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    public int sqlBatchLogicDeleteEntities(List<FxCasualtyLevelMapEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            StringBuffer sql = new StringBuffer("UPDATE fx_casualty_level_map set is_valid = '0',update_user='" + PlatformSessionUtils.getUserId() + "',update_time='" + PlatformDateUtils.getCurrentTimestamp() + "' WHERE uuid IN ( ");
            // 上万条数据
            for (FxCasualtyLevelMapEntity entity : list) {
                count++;
                String id = entity.getUuid();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }
}
